<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN" "http://www.w3.org/TR/html4/frameset.dtd">
<?php

session_start();
$beforeDeviationSC100 = 0;
$beforeDeviationSC95 = 0;
$beforeDeviationSC90 = 0;
$beforeDeviationSC85 = 0;
$beforeDeviationSC80 = 0;
$beforeDeviationSC75 = 0;
$beforeDeviationSC70 = 0;
$beforeDeviationSC65 = 0;
$beforeDeviationSC60 = 0;
$beforeDeviationSC55 = 0;
$beforeDeviationSC50 = 0;
$beforeDeviationSC45 = 0;
$beforeDeviationSC40 = 0;
$beforeDeviationSC35 = 0;
$beforeDeviationSC30 = 0;
$beforeDeviationSC25 = 0;
$beforeDeviationSC20 = 0;
$beforeDeviationSC15 = 0;
$beforeDeviationSC10 = 0;
$beforeDeviationSC5 = 0;
$beforeDeviationSC0 = 0;

$beforeDeviationPR100 = 0;
$beforeDeviationPR95 = 0;
$beforeDeviationPR90 = 0;
$beforeDeviationPR85 = 0;
$beforeDeviationPR80 = 0;
$beforeDeviationPR75 = 0;
$beforeDeviationPR70 = 0;
$beforeDeviationPR65 = 0;
$beforeDeviationPR60 = 0;
$beforeDeviationPR55 = 0;
$beforeDeviationPR50 = 0;
$beforeDeviationPR45 = 0;
$beforeDeviationPR40 = 0;
$beforeDeviationPR35 = 0;
$beforeDeviationPR30 = 0;
$beforeDeviationPR25 = 0;
$beforeDeviationPR20 = 0;
$beforeDeviationPR15 = 0;
$beforeDeviationPR10 = 0;
$beforeDeviationPR5 = 0;
$beforeDeviationPR0 = 0;

$afterDeviationSC100 = 0;
$afterDeviationSC95 = 0;
$afterDeviationSC90 = 0;
$afterDeviationSC85 = 0;
$afterDeviationSC80 = 0;
$afterDeviationSC75 = 0;
$afterDeviationSC70 = 0;
$afterDeviationSC65 = 0;
$afterDeviationSC60 = 0;
$afterDeviationSC55 = 0;
$afterDeviationSC50 = 0;
$afterDeviationSC45 = 0;
$afterDeviationSC40 = 0;
$afterDeviationSC35 = 0;
$afterDeviationSC30 = 0;
$afterDeviationSC25 = 0;
$afterDeviationSC20 = 0;
$afterDeviationSC15 = 0;
$afterDeviationSC10 = 0;
$afterDeviationSC5 = 0;
$afterDeviationSC0 = 0;

$afterDeviationPR100 = 0;
$afterDeviationPR95 = 0;
$afterDeviationPR90 = 0;
$afterDeviationPR85 = 0;
$afterDeviationPR80 = 0;
$afterDeviationPR75 = 0;
$afterDeviationPR70 = 0;
$afterDeviationPR65 = 0;
$afterDeviationPR60 = 0;
$afterDeviationPR55 = 0;
$afterDeviationPR50 = 0;
$afterDeviationPR45 = 0;
$afterDeviationPR40 = 0;
$afterDeviationPR35 = 0;
$afterDeviationPR30 = 0;
$afterDeviationPR25 = 0;
$afterDeviationPR20 = 0;
$afterDeviationPR15 = 0;
$afterDeviationPR10 = 0;
$afterDeviationPR5 = 0;
$afterDeviationPR0 = 0;

$countBeforeDeviationSC = 0;
$countAfterDeviationSC = 0;
$countAfterDeviationPR = 0;
$countBeforeDeviationPR = 0;

$beforeDeviationTotalAverageSC = 0;
$beforeDeviationTotalAveragePR = 0;
$afterDeviationTotalAverageSC = 0;
$afterDeviationTotalAveragePR = 0;

include 'conn.php';
$sql = mysql_query("Select CentreId from centre where CentreName='".$_SESSION["centreName"]."'");
while ($row = mysql_fetch_array($sql)) {
    $centreId = $row['CentreId'];
}
$query = "Select * from Tracking where Date<'$today' and CentreId=$centreId";
$today = date('Y-m-d');
$query = "Select * from Tracking where Date<'$today'";
$result = mysql_query($query);
while ($row = mysql_fetch_array($result)) {

    if ($row['Status'] == 'Not Exported') {
        $_SESSION["startDate"] = date('Y-m-01', strtotime($row['Date']));
        $_SESSION["endDate"] = date('Y-m-t', strtotime($row['Date']));
        
        $rowId = $row['TrackingId'];

        /*
          $centreDetail = "SELECT CentreId, CentreName FROM centre";
          $query_result = mysql_query($centreDetail);
          while ($result = mysql_fetch_assoc($query_result)) {
          $_SESSION['centreId'] = $result['CentreId'];
          header('Location: Reports/export.php?action=export');
          } */

        
        $startDate = $_SESSION["startDate"];
        $endDate = $_SESSION["endDate"];
        if ($centreId == 0) {
            $clwhere = "client.CentreId is not null";
//echo "testing ";
            $where = "CentreId is not null";
        } else {
            $clwhere = "client.CentreId=$centreId";
            $where = "CentreId=$centreId";
//echo "testing2";
        }

        require 'C:xampp/htdocs/Eldercare/Classes/PHPExcel.php';
        $objPHPExcel = new PHPExcel();
        $objPHPExcel->setActiveSheetIndex(0);
        $startDate1 = date('dmy', strtotime($startDate));
        $endDate1 = date('dmy', strtotime($endDate));
        $_SESSION["startDate1"] = $startDate1;
        $_SESSION["endDate1"] = $endDate1;
//$objConditional1 = new PHPExcel_Style_Conditional();
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(18);
        $objPHPExcel->getActiveSheet()->getStyle('3')->getFont()->setBold(true);
        $objPHPExcel->getProperties()->setTitle("EldercareReport" . $startDate . "to" . $endDate);
        $objPHPExcel->getActiveSheet()->setCellValue('A1', 'Eldercare Report from ' . $startDate1 . ' to ' . $endDate1 . ' for ' . $_SESSION["centreName"] . ' Centre');
        $objPHPExcel->getActiveSheet()->mergeCells('A1:N1');
        $objPHPExcel->getActiveSheet()->getStyle('A1:N1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


        include 'conn.php';
        $checkDateSNo = $startDate;
        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(9);
        $objPHPExcel->getActiveSheet()->mergeCells('C3:D3');
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(13);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(11);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('E3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->getStyle('F3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->getStyle('G3')->getAlignment()->setWrapText(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('A3', 'S/No');
        $objPHPExcel->getActiveSheet()->SetCellValue('B3', 'NRIC');
        $objPHPExcel->getActiveSheet()->SetCellValue('C3', 'Name');
        $objPHPExcel->getActiveSheet()->SetCellValue('E3', 'Citizenship');
        $objPHPExcel->getActiveSheet()->SetCellValue('F3', 'Before Deviation');
        $objPHPExcel->getActiveSheet()->SetCellValue('G3', 'After Deviation');
        $rowCount = 3;
        $count = 1;
        $column = 'H';
        while ($checkDateSNo <= $endDate) {
            $sqlhols = "Select count(*) from holiday where Date='$checkDateSNo'";
            $resulthols = mysql_query($sqlhols);

            while ($row = mysql_fetch_array($resulthols)) {
                if ($row['count(*)'] == 0) {
                    $day = date('l', strtotime($checkDateSNo));
                    if ($day != "Sunday" && $day != "Saturday") {
                        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, $count);
                        $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                        $count++;
                        $column++;
                    } else {
                        $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                    }
                } else {
                    $checkDateSNo = date('Y-m-d', strtotime('+1 day', strtotime($checkDateSNo)));
                }
            }
        }
        $objPHPExcel->getActiveSheet()->SetCellValue($column . $rowCount, 'Total');
        $rowCount++;
        $column = 'A';
        $ttlattendance = 0;
        include 'conn.php';
        $sql = "SELECT * from Client where $where";
        $result = mysql_query($sql);
        $sno = 1;
        while ($row = mysql_fetch_array($result)) {

            $objPHPExcel->getActiveSheet()->mergeCells('C' . $rowCount . ':D' . $rowCount);
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $rowCount, $sno);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $row['NRIC']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $row['CustomerName']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $row['Citizenship']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $row['BeforeDeviation']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $row['AfterDeviation']);

            $checkDate = $startDate;
            $workingday = 0;
            $attendance = 0;
            $column = 'H';
            while ($checkDate <= $endDate) {
                $sqlhols1 = "Select count(*) from holiday where Date='$checkDate'";
                $resulthols1 = mysql_query($sqlhols1);

                while ($rowhol = mysql_fetch_array($resulthols1)) {
                    if ($rowhol['count(*)'] == 0) {
                        $day = date('l', strtotime($checkDate));
                        if ($day != "Sunday" && $day != "Saturday") {
                            $sqlattendance = "Select * from attendancerecord where date(SignInTimeStamp)='$checkDate' and NRIC='" . $row['NRIC'] . "' and $where";
                            $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                            $workingday++;
                            $resultattendance = mysql_query($sqlattendance);
                            if (mysql_num_rows($resultattendance) == 1) {
                                $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '1');
                                $attendance++;
                            } else {
                                $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, '0');
                            }
                            $column++;
                        } else {
                            $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                        }
                    } else {
                        $checkDate = date('Y-m-d', strtotime('+1 day', strtotime($checkDate)));
                    }
                }
            }
            $ttlattendance += $attendance;
            $avg = $ttlattendance / $sno;
            $objPHPExcel->getActiveSheet()->setCellValue($column . $rowCount, $attendance);
//$str.="," . $attendance . "\n";
            $styleArray = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THIN
                    )
                )
            );
            $style2Array = array(
                'borders' => array(
                    'allborders' => array(
                        'style' => PHPExcel_Style_Border::BORDER_THICK, PHPExcel_Style_Border::BORDER_DOUBLE
                    )
                )
            );
            $objPHPExcel->getActiveSheet()->getStyle('A3:' . $column-- . $rowCount)->applyFromArray($styleArray);
            $sno++;
            $rowCount++;

            //Before Deviation
            if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 100) {
                $beforeDeviationSC100 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 95) {
                $beforeDeviationSC95 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 90) {
                $beforeDeviationSC90 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 85) {
                $beforeDeviationSC85 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 80) {
                $beforeDeviationSC80 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 75) {
                $beforeDeviationSC75 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 70) {
                $beforeDeviationSC70 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 65) {
                $beforeDeviationSC65 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 60) {
                $beforeDeviationSC60 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 55) {
                $beforeDeviationSC55 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 50) {
                $beforeDeviationSC50 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 45) {
                $beforeDeviationSC45 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 40) {
                $beforeDeviationSC40 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 35) {
                $beforeDeviationSC35 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 30) {
                $beforeDeviationSC30 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 25) {
                $beforeDeviationSC25 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 20) {
                $beforeDeviationSC20 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 15) {
                $beforeDeviationSC15 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 10) {
                $beforeDeviationSC10 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 5) {
                $beforeDeviationSC5 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['BeforeDeviation'] == 0) {
                $beforeDeviationSC0 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 100) {
                $beforeDeviationPR100 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 95) {
                $beforeDeviationPR95 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 90) {
                $beforeDeviationPR90 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 85) {
                $beforeDeviationPR85 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 80) {
                $beforeDeviationPR80 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 75) {
                $beforeDeviationPR75 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 70) {
                $beforeDeviationPR70 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 65) {
                $beforeDeviationPR65 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 60) {
                $beforeDeviationPR60 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 55) {
                $beforeDeviationPR55 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 50) {
                $beforeDeviationPR50 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 45) {
                $beforeDeviationPR45 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 40) {
                $beforeDeviationPR40 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 35) {
                $beforeDeviationPR35 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 30) {
                $beforeDeviationPR30 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 25) {
                $beforeDeviationPR25 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 20) {
                $beforeDeviationPR20 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 15) {
                $beforeDeviationPR15 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 10) {
                $beforeDeviationPR10 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 5) {
                $beforeDeviationPR5 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['BeforeDeviation'] == 0) {
                $beforeDeviationPR0 += $attendance;
            }

            //After Deviation
            if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 100) {
                $afterDeviationPR100 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 95) {
                $afterDeviationPR95 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 90) {
                $afterDeviationPR90 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 85) {
                $afterDeviationPR85 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 80) {
                $afterDeviationPR80 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 75) {
                $afterDeviationPR75 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 70) {
                $afterDeviationPR70 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 65) {
                $afterDeviationPR65 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 60) {
                $afterDeviationPR60 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 55) {
                $afterDeviationPR55 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 50) {
                $afterDeviationPR50 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 45) {
                $afterDeviationPR45 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 40) {
                $afterDeviationPR40 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 35) {
                $afterDeviationPR35 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 30) {
                $afterDeviationPR30 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 25) {
                $afterDeviationPR25 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 20) {
                $afterDeviationPR20 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 15) {
                $afterDeviationPR15 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 10) {
                $afterDeviationPR10 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 5) {
                $afterDeviationPR5 += $attendance;
            } else if ($row['Citizenship'] == 'PR' && $row['AfterDeviation'] == 0) {
                $afterDeviationPR0 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 100) {
                $afterDeviationSC100 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 95) {
                $afterDeviationSC95 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 90) {
                $afterDeviationSC90 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 85) {
                $afterDeviationSC85 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 80) {
                $afterDeviationSC80 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 75) {
                $afterDeviationSC75 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 70) {
                $afterDeviationSC70 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 65) {
                $afterDeviationSC65 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 60) {
                $afterDeviationSC60 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 55) {
                $afterDeviationSC55 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 50) {
                $afterDeviationSC50 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 45) {
                $afterDeviationSC45 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 40) {
                $afterDeviationSC40 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 35) {
                $afterDeviationSC35 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 30) {
                $afterDeviationSC30 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 25) {
                $afterDeviationSC25 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 20) {
                $afterDeviationSC20 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 15) {
                $afterDeviationSC15 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 10) {
                $afterDeviationSC10 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 5) {
                $afterDeviationSC5 += $attendance;
            } else if ($row['Citizenship'] == 'SC' && $row['AfterDeviation'] == 0) {
                $afterDeviationSC0 += $attendance;
            }
        }
        $objPHPExcel->getActiveSheet()->setCellValue('A' . $rowCount, 'Average Attendance:');
        $objPHPExcel->getActiveSheet()->mergeCells('A' . $rowCount . ':B' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('A' . $rowCount . ':C' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->setCellValue('C' . $rowCount, $avg);
        $rowCount++;
        $rowCount++;
        $rowCount1 = $rowCount;
        $rowOrgCount1 = $rowCount;
//Before Deviation first row
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->mergeCells('B' . $rowCount . ':G' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//After Deviation first row
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->mergeCells('I' . $rowCount . ':N' . $rowCount);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
//setting title header
        $objPHPExcel->getActiveSheet()->setCellValue('B' . $rowCount, 'Before Deviation');
        $objPHPExcel->getActiveSheet()->setCellValue('I' . $rowCount, 'After Deviation');
        $rowCount++;
//setting 2nd rows for BD
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, 'SC');
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, 'Average');
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, 'PR');
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, 'Average');
//setting 2nd rows for AD
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, 'SC');
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, 'Average');
        $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Subsidy Level');
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, 'PR');
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, 'Average');
        $rowCount++;
        $rowOrgCount = $rowCount;

//BD table info
//BEFORE DEVIATION DETAILS
        for ($i = 100; $i >= 0; $i-=5) {
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, $i . '%');
            $sqlbdsc = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='SC' and $where";
            $sqlbdpr = "SELECT count(*) from client where BeforeDeviation=$i and Citizenship='PR' and $where";
            $resultbdsc = mysql_query($sqlbdsc);
            $resultbdpr = mysql_query($sqlbdpr);

            while ($rowbdsc = mysql_fetch_array($resultbdsc)) {
                if ($rowbdsc['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $rowbdsc['count(*)']);
                    $countBeforeDeviationSC +=$rowbdsc['count(*)'];

                    if ($i == 100) {
                        $average = $beforeDeviationSC100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 95) {
                        $average = $beforeDeviationSC95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 90) {
                        $average = $beforeDeviationSC90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 85) {
                        $average = $beforeDeviationSC85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 80) {
                        $average = $beforeDeviationSC80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 75) {
                        $average = $beforeDeviationSC75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 70) {
                        $average = $beforeDeviationSC70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 65) {
                        $average = $beforeDeviationSC65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 60) {
                        $average = $beforeDeviationSC60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 55) {
                        $average = $beforeDeviationSC55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 50) {
                        $average = $beforeDeviationSC50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 45) {
                        $average = $beforeDeviationSC45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 40) {
                        $average = $beforeDeviationSC40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 35) {
                        $average = $beforeDeviationSC35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 30) {
                        $average = $beforeDeviationSC30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 25) {
                        $average = $beforeDeviationSC25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 20) {
                        $average = $beforeDeviationSC20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 15) {
                        $average = $beforeDeviationSC15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 10) {
                        $average = $beforeDeviationSC10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 5) {
                        $average = $beforeDeviationSC5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    } else if ($i == 0) {
                        $average = $beforeDeviationSC0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $average);
                        $beforeDeviationTotalAverageSC +=$average;
                    }
                    /* $sqlttlbdsc = "SELECT COUNT( * ) AS countttlbdsc
                      FROM attendancerecord, client
                      WHERE DATE( SignInTimeStamp )
                      BETWEEN  '$startDate'
                      AND  '$endDate'
                      AND BeforeDeviation =$i AND Citizenship='SC' AND $clwhere
                      AND client.NRIC = attendancerecord.NRIC";
                      $resulttlbdsc = mysql_query($sqlttlbdsc);
                      while ($rowttlbdsc = mysql_fetch_array($resulttlbdsc)) {
                      $roundttlbdsc = round($rowttlbdsc['countttlbdsc'] - $workingday / $rowbdsc['count(*)'], 3);
                      $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $roundttlbdsc);
                      } */
                }
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, $i . '%');

            while ($rowbdpr = mysql_fetch_array($resultbdpr)) {
                if ($rowbdpr['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $rowbdpr['count(*)']);
                    $countBeforeDeviationPR +=$rowbdpr['count(*)'];

                    if ($i == 100) {
                        $average = $beforeDeviationPR100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 95) {
                        $average = $beforeDeviationPR95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 90) {
                        $average = $beforeDeviationPR90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 85) {
                        $average = $beforeDeviationPR85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 80) {
                        $average = $beforeDeviationPR80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 75) {
                        $average = $beforeDeviationPR75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 70) {
                        $average = $beforeDeviationPR70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 65) {
                        $average = $beforeDeviationPR65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 60) {
                        $average = $beforeDeviationPR60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 55) {
                        $average = $beforeDeviationPR55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 50) {
                        $average = $beforeDeviationPR50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 45) {
                        $average = $beforeDeviationPR45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 40) {
                        $average = $beforeDeviationPR40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 35) {
                        $average = $beforeDeviationPR35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 30) {
                        $average = $beforeDeviationPR30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 25) {
                        $average = $beforeDeviationPR25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 20) {
                        $average = $beforeDeviationPR20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 15) {
                        $average = $beforeDeviationPR15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 10) {
                        $average = $beforeDeviationPR10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 5) {
                        $average = $beforeDeviationPR5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    } else if ($i == 0) {
                        $average = $beforeDeviationPR0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $average);
                        $beforeDeviationTotalAveragePR +=$average;
                    }
                    /* $sqlttlbdpr = "SELECT COUNT( * ) AS countttlbdpr
                      FROM attendancerecord, client
                      WHERE DATE( SignInTimeStamp )
                      BETWEEN  '$startDate'
                      AND  '$endDate'
                      AND BeforeDeviation =$i AND Citizenship='PR' AND $clwhere
                      AND client.NRIC = attendancerecord.NRIC";
                      $resulttlbdpr = mysql_query($sqlttlbdpr);
                      while ($rowttlbdpr = mysql_fetch_array($resulttlbdpr)) {
                      $roundttlbdpr = round($rowttlbdsc['countttlbdpr'] - $workingday / $rowbdpr['count(*)'], 3);
                      $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $roundttlbdpr);
                      } */
                }
            }
            $rowCount++;
        }

        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':G' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('B' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('C' . $rowCount, $countBeforeDeviationSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('D' . $rowCount, $beforeDeviationTotalAverageSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('E' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('F' . $rowCount, $countBeforeDeviationPR);
        $objPHPExcel->getActiveSheet()->SetCellValue('G' . $rowCount, $beforeDeviationTotalAveragePR);
//AFTER DEVIATION TABLE INFO
        for ($j = 100; $j >= 0; $j-=5) {
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowOrgCount, $j . '%');
            $sqladsc = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='SC' and $where";
            $sqladpr = "SELECT count(*) from client where AfterDeviation=$j and Citizenship='PR' and $where";
            $resultadsc = mysql_query($sqladsc);
            $resultadpr = mysql_query($sqladpr);

            while ($rowadsc = mysql_fetch_array($resultadsc)) {
                if ($rowadsc['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, '0');
                } else {

                    $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowOrgCount, $rowadsc['count(*)']);
                    $countAfterDeviationSC +=$rowadsc['count(*)'];
                    if ($j == 100) {
                        $average = $afterDeviationSC100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 95) {
                        $average = $afterDeviationSC95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 90) {
                        $average = $afterDeviationSC90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 85) {
                        $average = $afterDeviationSC85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 80) {
                        $average = $afterDeviationSC80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 75) {
                        $average = $afterDeviationSC75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 70) {
                        $average = $afterDeviationSC70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 65) {
                        $average = $afterDeviationSC65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 60) {
                        $average = $afterDeviationSC60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 55) {
                        $average = $afterDeviationSC55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 50) {
                        $average = $afterDeviationSC50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 45) {
                        $average = $afterDeviationSC45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 40) {
                        $average = $afterDeviationSC40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 35) {
                        $average = $afterDeviationSC35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 30) {
                        $average = $afterDeviationSC30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 25) {
                        $average = $afterDeviationSC25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 20) {
                        $average = $afterDeviationSC20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 15) {
                        $average = $afterDeviationSC15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 10) {
                        $average = $afterDeviationSC10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 5) {
                        $average = $afterDeviationSC5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    } else if ($j == 0) {
                        $average = $afterDeviationSC0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $average);
                        $afterDeviationTotalAverageSC += $average;
                    }
                    /* $sqlttladsc = "SELECT COUNT( * ) AS countttladsc
                      FROM attendancerecord, client
                      WHERE DATE( SignInTimeStamp )
                      BETWEEN  '$startDate'
                      AND  '$endDate'
                      AND AfterDeviation =$j AND Citizenship='SC' AND $clwhere
                      AND client.NRIC = attendancerecord.NRIC";
                      $resulttladsc = mysql_query($sqlttladsc);
                      while ($rowttladsc = mysql_fetch_array($resulttladsc)) {
                      $roundttladsc = round($rowttladsc['countttladsc'] - $workingday / $rowadsc['count(*)'], 3);
                      $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowOrgCount, $roundttladsc);
                      } */
                }
            }

            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowOrgCount, $j . '%');

            while ($rowadpr = mysql_fetch_array($resultadpr)) {
                if ($rowadpr['count(*)'] == 0) {
                    $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, '0');
                    $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, '0');
                } else {
                    $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowOrgCount, $rowadpr['count(*)']);
                    $countAfterDeviationPR +=$rowadpr['count(*)'];

                    if ($j == 100) {
                        $average = $afterDeviationPR100 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 95) {
                        $average = $afterDeviationPR95 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 90) {
                        $average = $afterDeviationPR90 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 85) {
                        $average = $afterDeviationPR85 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 80) {
                        $average = $afterDeviationPR80 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 75) {
                        $average = $afterDeviationPR75 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 70) {
                        $average = $afterDeviationPR70 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 65) {
                        $average = $afterDeviationPR65 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 60) {
                        $average = $afterDeviationPR60 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 55) {
                        $average = $afterDeviationPR55 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 50) {
                        $average = $afterDeviationPR50 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 45) {
                        $average = $afterDeviationPR45 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 40) {
                        $average = $afterDeviationPR40 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 35) {
                        $average = $afterDeviationPR35 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 30) {
                        $average = $afterDeviationPR30 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 25) {
                        $average = $afterDeviationPR25 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 20) {
                        $average = $afterDeviationPR20 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 15) {
                        $average = $afterDeviationPR15 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 10) {
                        $average = $afterDeviationPR10 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 5) {
                        $average = $afterDeviationPR5 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    } else if ($j == 0) {
                        $average = $afterDeviationPR0 / $workingday;
                        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $average);
                        $afterDeviationTotalAveragePR += $average;
                    }
                    /* $sqlttladpr = "SELECT COUNT( * ) AS countttladpr
                      FROM attendancerecord, client
                      WHERE DATE( SignInTimeStamp )
                      BETWEEN  '$startDate'
                      AND  '$endDate'
                      AND AfterDeviation =$j AND Citizenship='PR' AND $clwhere
                      AND client.NRIC = attendancerecord.NRIC";
                      $resulttladpr = mysql_query($sqlttladpr);
                      while ($rowttladpr = mysql_fetch_array($resulttladpr)) {
                      $roundttladpr = round($rowttladsc['countttladpr'] - $workingday / $rowadpr['count(*)'], 3);
                      $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowOrgCount, $roundttladpr);
                      } */
                }
            }
            $rowOrgCount++;
        }
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowCount . ':N' . $rowCount)->getFont()->setBold(true);
        $objPHPExcel->getActiveSheet()->SetCellValue('I' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('J' . $rowCount, $countAfterDeviationSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('K' . $rowCount, $afterDeviationTotalAverageSC);
        $objPHPExcel->getActiveSheet()->SetCellValue('L' . $rowCount, 'Total');
        $objPHPExcel->getActiveSheet()->SetCellValue('M' . $rowCount, $countAfterDeviationPR);
        $objPHPExcel->getActiveSheet()->SetCellValue('N' . $rowCount, $afterDeviationTotalAveragePR);

        $rowCount = $rowCount - 1;
        $rowOrgCount = $rowOrgCount - 1;
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount1 . ':' . 'G' . $rowCount)->applyFromArray($styleArray);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount1 . ':' . 'N' . $rowOrgCount)->applyFromArray($styleArray);
        $rowCount++;
        $rowOrgCount++;
        $objPHPExcel->getActiveSheet()->getStyle('B' . $rowCount . ':' . 'G' . $rowCount)->applyFromArray($style2Array);
        $objPHPExcel->getActiveSheet()->getStyle('I' . $rowOrgCount . ':' . 'N' . $rowOrgCount)->applyFromArray($style2Array);

        $query = "UPDATE Tracking SET Status='Exported' WHERE TrackingId=$rowId";
        $result = mysql_query($query);

        //creating the excel file
        $todayEndDate = date('Y-m-t', strtotime($today));
        $insertquery = "INSERT INTO tracking(Date,Status,CentreId) VALUES('$todayEndDate','Not Exported',$centreId)";
        $insertresult = mysql_query($insertquery);

        //$objPHPExcel->disconnectWorksheets();



        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save(str_replace('export.php', 'EldercareReport' . $startDate1 . 'to' . $endDate1 . '-' . $_SESSION["centreName"] . '.xlsx', __FILE__));
        include_once('email.php');
        header('Location: ../AttendanceHome.php');
        //header("Location: Attendance/export.php");
    }
}
?>